<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Connecting to the MySQL database</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="MySQL, Perl, DBI, connecting, tutorial, database, programming, development">
<meta name="description" content="In this chapter of the MySQL Perl tutorial, we create a connection to
the database.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Connecting to the MySQL database</h1>

<p>
This part of the MySQL Perl tutorial will show, how to create a database
connection to the database. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
The first step is to connect to the database. We use the <code>connect()</code>
DBI method to establish a connection. The <code>disconnect()</code> method is 
used to close the database connection.
</p>

<pre>
$dbh = DBI->connect($dsn, $username, $password)
    or die $DBI::errstr;
$dbh = DBI->connect($dsn, $username, $password, \%attr)
    or die $DBI::errstr;
</pre>

<p>
The <code>connect()</code> method establishes a database connection to the requested
data source. It returns a database handle object if the connection
succeeds. We use the <code>disconnect()</code> method to terminate the connection.
</p>

<p>
The $dsn is the data source name. It is a string that tells the Perl DBI module, what
kind of driver it should load and the location of the database to which the connection
is going to be created. 
</p>

<pre>
dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port
</pre>

<p>
The above strings are examples of data source names in Perl DBI. 
</p>

<pre>
dbi:mysql:dbname=mydb
</pre>

<p>
We are going to use this data source name. The dsn starts always with the dbi: substring. 
Then we have the driver name. In our case the driver name is mysql. The third part is the
database name. We will work with mydb throughout this tutorial. 
</p>

<p>
The $username and the $password are the user name and his password that are
needed for authentication. The final parameter is a reference to 
hash, in which we can set attributes to alter the default settings of a connection. 
For example the RaiseError attribute can be used to force errors to raise exceptions 
rather than return error codes. The HandleError attribute can be used to  provide a 
subroutine which is called in case of error. The AutoCommit attribute sets or unsets 
the autocommit mode. 
</p>

<p>
The $DBI::errstr is a DBI dynamic attribute which returns the native database engine 
error message. In case the connection fails, this message is displayed and the
script is aborted. 
</p>



<h2>Version</h2>

<p>
In the first code example, we will get the version of the MySQL database.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare("SELECT VERSION()");
$sth->execute();

my $ver = $sth->fetch();

print @$ver;
print "\n";

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the above Perl script we connect to the previously created
mydb database. We execute an SQL statement which returns the
version of the MySQL database. 
</p>

<pre class="explanation">
use DBI;
</pre>

<p>
We use the Perl DBI module to connect to the MySQL database.
</p>

<pre class="explanation">
my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;
</pre>

<p>
Here we connect to the mydb database. The first parameter is the
data source name. In the string we specify the database driver and the
database name. The second parameter is the user name. The third parameter
is the user password. The last parameter is the database options. 
We set the RaiseError option to 1. This will 
cause exceptions to be raised insted of returning error codes.
</p>

<pre class="explanation">
my $sth = $dbh->prepare("SELECT VERSION()");
$sth->execute();
</pre>

<p>
The <code>prepare()</code> method prepares an SQL statement for later execution. 
The <code>execute()</code> method executes the SQL statement. 
</p>

<pre class="explanation">
my $ver = $sth->fetch();
</pre>

<p>
We fetch the data. 
</p>

<pre class="explanation">
print @$ver;
print "\n";
</pre>

<p>
We print the data that we have retrieved to the console.
</p>

<pre class="explanation">
$sth->finish();
</pre>

<p>
Here we indicate that no more data will be fetched from this 
statement handle. 
</p>

<pre class="explanation">
$dbh->disconnect();
</pre>

<p>
We close the connection to the database. 
</p>

<pre>
$ ./version.pl
5.1.62-0ubuntu0.11.10.1
</pre>

<p>
Executing the verion.pl script we get the version of the MySQL
database. 
</p>


<h2>Inserting data</h2>

<p>
We will create a Cars table and insert several rows to it.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",
    "34klq*",
    { RaiseError => 1}
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Cars");
$dbh->do("CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, Price INT) ENGINE=InnoDB");
$dbh->do("INSERT INTO Cars VALUES(1,'Audi',52642)");
$dbh->do("INSERT INTO Cars VALUES(2,'Mercedes',57127)");
$dbh->do("INSERT INTO Cars VALUES(3,'Skoda',9000)");
$dbh->do("INSERT INTO Cars VALUES(4,'Volvo',29000)");
$dbh->do("INSERT INTO Cars VALUES(5,'Bentley',350000)");
$dbh->do("INSERT INTO Cars VALUES(6,'Citroen',21000)");
$dbh->do("INSERT INTO Cars VALUES(7,'Hummer',41400)");
$dbh->do("INSERT INTO Cars VALUES(8,'Volkswagen',21600)");

$dbh->disconnect();
</pre>

<p>
The above script creates a Cars table and inserts 8 rows into the
table. 
</p>

<pre class="explanation">
$dbh->do("CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, Price INT) ENGINE=InnoDB");
</pre>

<p>
The <code>do()</code> method executes the SQL statements. It combines two 
method calls, <code>prepare()</code> and <code>execute()</code> into one single
call. The <code>do()</code> method is used for non-select statements. 
</p>

<pre class="explanation">
$dbh->do("INSERT INTO Cars VALUES(1,'Audi',52642)");
$dbh->do("INSERT INTO Cars VALUES(2,'Mercedes',57127)");
</pre>

<p>
These two lines insert two cars into the table. Note that by default, we are in
the <b>autocommit mode</b>, where all changes to the table are immediately effective. 
</p>

<pre>
mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.01 sec)
</pre>

<p>
This is the data that we have written to the Cars table.
</p>


<h2>The last inserted row id</h2>

<p>
Sometimes, we need to determine the id of the last inserted
row. In Perl DBI, we use the <code>last_insert_id()</code> method
to find it. 
</p>


<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT, Name TEXT)");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");
    
my $id = $dbh->last_insert_id("", "", "Friends", "");
print "The last Id of the inserted row is $id\n";

$dbh->disconnect();
</pre>

<p>
We create a new Friends table. The Id is automatically incremented. 
</p>

<pre class="explanation">
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT, Name TEXT)");
</pre>

<p>
This is the SQL statement to create a Friends table. 
The AUTO_INCREMENT attribute is used to generate a unique id for new rows.
</p>

<pre class="explanation">
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");
</pre>

<p>
These five SQL statements insert five rows into the Friends table. 
</p>

<pre class="explanation">
my $id = $dbh->last_insert_id("", "", "Friends", "");
</pre>

<p>
Using the <code>last_insert_id()</code> method, we get the last inserted row id.  
</p>

<pre>
$ ./last_rowid.pl
The last Id of the inserted row is 5
</pre>

<p>
We see the output of the script. 
</p>


<h2>Fetching data</h2>

<p>
In the last example of this chapter we fetch some data. More about data 
fetching will be discussed in the Queries chapter. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );  
$sth->execute();
      
my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";

my $fields = $sth->{NUM_OF_FIELDS};
print "We have selected $fields field(s)\n";

my $rows = $sth->rows();
print "We have selected $rows row(s)\n";

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the example we fetch a row from the Cars table. We will also find out how 
many fields &amp; rows we have selected. 
</p>

<pre class="explanation">
my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );  
$sth->execute();
</pre>

<p>
We prepare an SQL statement with the <code>prepare()</code> method. 
The SQL string is sent to the MySQL database engine for processing. 
Its syntax and validity is checked. The method returns a statement handle.
Then the SQL statement is executed. The data is prepared to be sent
to the client program. 
</p>

<pre class="explanation">
my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";
</pre>

<p>
The data is retrieved from the database with the <code>fetchrow()</code>
method. The method returns one row from the table in form of a Perl list. 
</p>

<pre class="explanation">
my $fields = $sth->{NUM_OF_FIELDS};
</pre>

<p>
The NUM_OF_FIELDS is a statement handle attribute
which gives us the number of returned fields. In our case we have
three fields returned. Id, Name and Price. 
</p>

<pre class="explanation">
my $rows = $sth->rows();
</pre>

<p>
We get the number of selected rows. We have retrieved only one row
from the table. The <code>rows()</code> method returns the number of
affected rows. It can be used for SELECT, UPDATE and DELETE SQL 
statements. 
</p>

<pre>
$ ./fetchrow.pl
1 Audi 52642
We have selected 3 field(s)
We have selected 1 row(s)
</pre>

<p>
Output of the fetchrow.pl script. 
</p>

<p>
In this chapter of the MySQL Perl tutorial, we have shown how to establish 
a database connection to the MySQL database. We have explained scripts
which do some basic work with a database.
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 6, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

